﻿Imports System.Collections.Specialized

Public Class MoneyMonth
    Private _ParamsValues As New Hashtable
    Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click

        _ParamsValues.Clear()

        Dim date3 As Date = New Date(dt月别开始时间.Value.Year, dt月别开始时间.Value.Month, 1)
        Dim date4 As Date = New Date(dt月别结束时间.Value.Year, dt月别结束时间.Value.Month, 1)
        If date3 > date4 Then
            ShowMessageBox("开始时间不能大于结束时间")
            dt月别开始时间.Focus()
            Exit Sub
        End If

        Dim date1 As New Date(Me.dt月别开始时间.Value.Year, Me.dt月别开始时间.Value.Month, 1)
        Dim date2 As New Date(Me.dt月别结束时间.Value.Year, Me.dt月别结束时间.Value.Month, Date.DaysInMonth(Me.dt月别结束时间.Value.Year, Me.dt月别结束时间.Value.Month))
        _ParamsValues.Add("开始时间", date1.ToString)
        _ParamsValues.Add("结束时间", date2.ToString)

        If Me.txtUsername.Text.Trim <> "" AndAlso Me.CheckBox1.Checked Then
            _ParamsValues.Add("用户", CStr(Me.txtUsername.Tag))
        End If

        Try
            Dim englishFolder As String = System.IO.Path.Combine(GetCurrentDirectory, "Report")
            If Not System.IO.Directory.Exists(englishFolder) Then
                System.IO.Directory.CreateDirectory(englishFolder)
            End If

            Dim englishPath As String = System.IO.Path.Combine(englishFolder, "In_Detail.xls")
            Dim bCreate As Boolean = True
            If System.IO.File.Exists(englishPath) Then
                System.IO.File.Delete(englishPath)
            End If

            If bCreate Then
                Dim dt As System.Data.DataTable = CreateDataSourceInMonth()
                If dt.Rows.Count = 0 Then
                    ShowMessageBox("没有数据")
                    Exit Sub
                End If

                Dim realindex As Integer = 1
                Dim newTable As DataTable = dt.Clone
                Dim dv As DataView = dt.DefaultView
                Dim 合计金额 As Decimal
                Dim dr As DataRow

                If chk按用户.Checked = False Then
                    Dim englishCreate As New OutYear(englishPath)
                    AddHandler englishCreate.PrintProcess, AddressOf PrintProcess

                    合计金额 = dt.Compute("sum(收入)", "")
                    dr = dt.NewRow
                    dr("月份") = "合计金额"
                    dr("收入") = 合计金额

                    合计金额 = dt.Compute("sum(支出)", "")
                    dr("支出") = 合计金额

                    合计金额 = dt.Compute("sum(统计)", "")
                    dr("统计") = 合计金额

                    dt.Rows.Add(dr)

                    englishCreate.DoOutData(dt)
                    RemoveHandler englishCreate.PrintProcess, AddressOf PrintProcess
                Else
                    Dim englishCreate As New OutYear_User(englishPath)
                    englishCreate.报表记录条数字 = Me.报表记录条数字
                    englishCreate.报表字体 = Me.报表字体

                    AddHandler englishCreate.PrintProcess, AddressOf PrintProcess

                    Dim dtUser As DataTable = ct.FillTable("select * from WysInputUser where type=0 order by userindex")

                    If (_ParamsValues.Contains("用户")) Then
                        dtUser = ct.FillTable("select * from WysInputUser where type=0 and id ='" & _ParamsValues.Item("用户").ToString & "'")
                    End If

                    If dtUser.Rows.Count > 0 Then
                        For index As Integer = 0 To dtUser.Rows.Count - 1
                            dv.RowFilter = "用户='" + CStr(dtUser.Rows(index)("username")) + "'"
                            newTable = dv.ToTable
                            If newTable.Rows.Count > 0 Then

                                合计金额 = newTable.Compute("sum(收入)", "")
                                dr = newTable.NewRow
                                dr("月份") = "合计金额"
                                dr("收入") = 合计金额

                                合计金额 = newTable.Compute("sum(支出)", "")
                                dr("支出") = 合计金额

                                合计金额 = newTable.Compute("sum(统计)", "")
                                dr("统计") = 合计金额

                                newTable.Rows.Add(dr)

                               englishCreate.DoOutData(newTable, realindex, False)

                                realindex = realindex + 1
                            End If
                        Next

                        '开始出合计
                        Dim dt合计 As DataTable = dt.Clone
                        Dim dt月份 As DataTable = dt.DefaultView.ToTable(True, "月份")
                        

                        If dt月份.Rows.Count > 0 Then
                            For index As Integer = 0 To dt月份.Rows.Count - 1
                                合计金额 = dt.Compute("sum(收入)", "月份='" & dt月份.Rows(index)("月份") & "'")
                                dr = dt合计.NewRow
                                dr("月份") = dt月份.Rows(index)("月份")
                                dr("收入") = 合计金额

                                合计金额 = dt.Compute("sum(支出)", "月份='" & dt月份.Rows(index)("月份") & "'")
                                dr("支出") = 合计金额

                                合计金额 = dt.Compute("sum(统计)", "月份='" & dt月份.Rows(index)("月份") & "'")
                                dr("统计") = 合计金额

                                dr("用户") = "合计"

                                dt合计.Rows.Add(dr)
                            Next

                            合计金额 = dt合计.Compute("sum(收入)", "")
                            dr = dt合计.NewRow
                            dr("月份") = "合计金额"
                            dr("收入") = 合计金额

                            合计金额 = dt合计.Compute("sum(支出)", "")
                            dr("支出") = 合计金额

                            合计金额 = dt合计.Compute("sum(统计)", "")
                            dr("统计") = 合计金额

                            dr("用户") = "合计"
                            dt合计.Rows.Add(dr)

                        End If

                        englishCreate.DoOutData(dt合计, realindex, False)
                        realindex = realindex + 1
                        '开始打印趋势图                        
                        '收入趋势图
                        englishCreate.DoOutDataA(dt合计, realindex, False, 0)
                        realindex = realindex + 1
                        '支出趋势图
                        englishCreate.DoOutDataA(dt合计, realindex, False, 1)
                        realindex = realindex + 1
                        '节余趋势图
                        englishCreate.DoOutDataA(dt合计, realindex, True, 2)
                        realindex = realindex + 1
                        englishCreate.特别保存()
                    End If

                    RemoveHandler englishCreate.PrintProcess, AddressOf PrintProcess
                End If

                '打开文件
                Using pro As New Process
                    pro.StartInfo.FileName = englishPath
                    pro.Start()
                End Using

            End If
        Catch ex As Exception
            ShowMessageBox(ex.ToString)
        End Try

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

#Region "窗体数据保存的重载方法 A-2010-09-06"
    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)

        dtData.Rows.Add(New Object() {"开始时间.年", "", dt月别开始时间.Value.Year.ToString})
        dtData.Rows.Add(New Object() {"开始时间.月", "", dt月别开始时间.Value.Month.ToString})

        dtData.Rows.Add(New Object() {"结束时间.年", "", dt月别结束时间.Value.Year.ToString})
        dtData.Rows.Add(New Object() {"结束时间.月", "", dt月别结束时间.Value.Month.ToString})

        If chk按用户.Checked Then
            dtData.Rows.Add(New Object() {"分类1", "", "按用户"})
        End If

    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)
        If dtDbData.Rows.Count > 0 Then

            If 查找数据("分类1", "", "", dtDbData) = "按用户" Then
                chk按用户.Checked = True
            End If

        End If

        Dim date1 As New Date(CInt(查找数据("开始时间.年", "", Me.dt月别开始时间.Value.Year.ToString, dtDbData)), CInt(查找数据("开始时间.月", "", "1", dtDbData)), 1)
        Me.dt月别开始时间.Value = date1
        date1 = New Date(CInt(查找数据("结束时间.年", "", Me.dt月别开始时间.Value.Year.ToString, dtDbData)), CInt(查找数据("结束时间.月", "", "1", dtDbData)), 1)
        Me.dt月别结束时间.Value = date1
    End Sub

#End Region

    Private Sub btnUsername_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUsername.Click
        Using frm As wys.Control.Vb.ListForm = New wys.Control.Vb.ListForm
            frm.strName = Me.strName
            frm.iType = Me.iType
            frm.SQL = "select ID,UserName as 名称 from WysInputUser where type=0 order by userindex"
            Dim 显示列 As New StringCollection
            显示列.Add("名称")
            frm.Width = 100
            frm.Height = 200
            frm.显示列 = 显示列
            frm.ShowDialog()
            If frm.返回值.Count > 0 Then
                txtUsername.Tag = CStr(frm.返回值(0))
                txtUsername.Text = CStr(frm.返回值(1))
            End If
        End Using
    End Sub

    Private Sub MoneyOut_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.txtUsername.Tag = ""
    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
        If Me.CheckBox1.Checked Then
            Me.txtUsername.Enabled = True
            Me.btnUsername.Enabled = True
        Else
            txtUsername.Text = ""
            Me.txtUsername.Enabled = False
            Me.btnUsername.Enabled = False
        End If
    End Sub

#Region "创建收入月别数据源"
    Private Function CreateDataSourceInMonth() As DataTable

        Dim strSql As String = String.Empty
        Dim strSql1 As String = String.Empty
        Dim dr As DataRow

        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("用户")
        dt.Columns.Add("月份")
        dc = dt.Columns.Add("收入", GetType(Decimal))
        dc.DefaultValue = 0

        dt.Columns.Add("支出", GetType(Decimal))
        dt.Columns.Add("统计", GetType(Decimal))
        dc = dt.Columns.Add("用户ID")
        dc.DefaultValue = ""
        '抽数据
        Dim Year1 As Integer = Me.dt月别开始时间.Value.Year
        Dim Year2 As Integer = Me.dt月别结束时间.Value.Year

        If chk按用户.Checked = False Then
            For Year As Integer = dt月别开始时间.Value.Year To dt月别结束时间.Value.Year

                Dim 开始月 As Integer = 1
                Dim 结束月 As Integer = 12
                If Year = dt月别开始时间.Value.Year Then
                    开始月 = dt月别开始时间.Value.Month
                End If

                If Year = dt月别结束时间.Value.Year Then
                    结束月 = dt月别结束时间.Value.Month
                End If

                For endmouth As Integer = 开始月 To 结束月
                    Dim day As Integer = DateTime.DaysInMonth(Year, endmouth)
                    Dim strBegin As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + 1.ToString("00")
                    Dim strEnd As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")

                    strSql = "select  sum(count) as count from WysInput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"
                    If (_ParamsValues.Contains("用户")) Then
                        strSql += " AND WysInput.UserName ='" & _ParamsValues.Item("用户").ToString & "'"
                    End If

                    strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"
                    If (_ParamsValues.Contains("用户")) Then
                        strSql1 += " AND WysOutput.UserName ='" & _ParamsValues.Item("用户").ToString & "'"
                    End If
                    dr = dt.NewRow

                    If endmouth = 1 Then
                        dr(1) = "01月"
                    ElseIf endmouth = 2 Then
                        dr(1) = "02月"
                    ElseIf endmouth = 3 Then
                        dr(1) = "03月"
                    ElseIf endmouth = 4 Then
                        dr(1) = "04月"
                    ElseIf endmouth = 5 Then
                        dr(1) = "05月"
                    ElseIf endmouth = 6 Then
                        dr(1) = "06月"
                    ElseIf endmouth = 7 Then
                        dr(1) = "07月"
                    ElseIf endmouth = 8 Then
                        dr(1) = "08月"
                    ElseIf endmouth = 9 Then
                        dr(1) = "09月"
                    ElseIf endmouth = 10 Then
                        dr(1) = "10月"
                    ElseIf endmouth = 11 Then
                        dr(1) = "11月"
                    ElseIf endmouth = 12 Then
                        dr(1) = "12月"
                    End If

                    dr(1) = Year.ToString & "年" & CStr(dr(1))
                    Dim dt2 As DataTable = ct.FillTable(strSql)
                    If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                        dr(2) = 结果(dt2.Rows(0)(0))
                    Else
                        dr(2) = 0
                    End If
                    dt2 = ct.FillTable(strSql1)
                    If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                        dr(3) = 结果(dt2.Rows(0)(0))
                    Else
                        dr(3) = 0
                    End If
                    dr(4) = CDec(dr(2)) - CDec(dr(3))
                    dt.Rows.Add(dr)
                Next
            Next
        Else

            Dim dt用户 As DataTable = ct.FillTable("select * from WysInputUser where type=0 order by UserIndex")
            If dt用户.Rows.Count > 0 Then
                For Each dr用户 As DataRow In dt用户.Rows
                    For Year As Integer = dt月别开始时间.Value.Year To dt月别结束时间.Value.Year

                        Dim 开始月 As Integer = 1
                        Dim 结束月 As Integer = 12
                        If Year = dt月别开始时间.Value.Year Then
                            开始月 = dt月别开始时间.Value.Month
                        End If

                        If Year = dt月别结束时间.Value.Year Then
                            结束月 = dt月别结束时间.Value.Month
                        End If

                        For endmouth As Integer = 开始月 To 结束月
                            Dim day As Integer = DateTime.DaysInMonth(Year, endmouth)
                            Dim strBegin As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + 1.ToString("00")
                            Dim strEnd As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")

                            strSql = "select  sum(count) as count from WysInput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "' AND username='" + CStr(dr用户("id")) + "'"
                            If (_ParamsValues.Contains("用户")) Then
                                strSql += " AND WysInput.UserName ='" & _ParamsValues.Item("用户").ToString & "'"
                            End If

                            strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "' AND username='" + CStr(dr用户("id")) + "'"
                            If (_ParamsValues.Contains("用户")) Then
                                strSql1 += " AND WysOutput.UserName ='" & _ParamsValues.Item("用户").ToString & "'"
                            End If
                            dr = dt.NewRow
                            dr(0) = dr用户("username")

                            If endmouth = 1 Then
                                dr(1) = "01月"
                            ElseIf endmouth = 2 Then
                                dr(1) = "02月"
                            ElseIf endmouth = 3 Then
                                dr(1) = "03月"
                            ElseIf endmouth = 4 Then
                                dr(1) = "04月"
                            ElseIf endmouth = 5 Then
                                dr(1) = "05月"
                            ElseIf endmouth = 6 Then
                                dr(1) = "06月"
                            ElseIf endmouth = 7 Then
                                dr(1) = "07月"
                            ElseIf endmouth = 8 Then
                                dr(1) = "08月"
                            ElseIf endmouth = 9 Then
                                dr(1) = "09月"
                            ElseIf endmouth = 10 Then
                                dr(1) = "10月"
                            ElseIf endmouth = 11 Then
                                dr(1) = "11月"
                            ElseIf endmouth = 12 Then
                                dr(1) = "12月"
                            End If

                            dr(1) = Year.ToString & "年" & CStr(dr(1))
                            Dim dt2 As DataTable = ct.FillTable(strSql)
                            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                                dr(2) = 结果(dt2.Rows(0)(0))
                            Else
                                dr(2) = 0
                            End If
                            dt2 = ct.FillTable(strSql1)
                            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                                dr(3) = 结果(dt2.Rows(0)(0))
                            Else
                                dr(3) = 0
                            End If
                            dr(4) = CDec(dr(2)) - CDec(dr(3))
                            dr(5) = dr用户("ID")
                            dt.Rows.Add(dr)
                        Next
                    Next
                Next
            End If
        End If


        Return dt

    End Function
#End Region

    Public Function 结果(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            ob = 0
        End If
        Return CDec(ob)
    End Function

    Private Sub PrintProcess(ByVal content As String)
        lbl1000.Text = content
        System.Windows.Forms.Application.DoEvents()
    End Sub

#Region "报表必须使用"
    Private _报表字体 As String = "宋体"
    Public Overrides Property 报表字体() As String
        Get
            Return _报表字体
        End Get
        Set(ByVal value As String)
            _报表字体 = value
        End Set
    End Property

    Private _报表记录条数字 As Integer = 55
    Public Overrides Property 报表记录条数字() As Integer
        Get
            Return _报表记录条数字
        End Get
        Set(ByVal value As Integer)
            _报表记录条数字 = value
        End Set
    End Property
#End Region
End Class
